package com.ktgj.hotel.utils.jdbc;

//import com.ktgj.money.moneysystem.entity.Constants;
//import org.slf4j.Logger;
//import org.slf4j.LoggerFactory;

import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * JDBC 工具类
 *
 * @author 山楂罐头
 * @date 2019/8/6
 */
public class JdbcUtil implements Serializable {
    private static final long serialVersionUID = 1382377502744233953L;
    //private static final Logger logger = LoggerFactory.getLogger(JdbcUtil.class);

    // 数据库连接对象
    private Connection connection = null;
    // 执行静态SQL语句
    private Statement statement = null;
    // 执行动态SQL语句
    private PreparedStatement preparedStatement = null;
    // 结果集
    private ResultSet resultSet = null;


    // 数据库连接URL
    private String url;
    // 用户名
    private String username;
    // 密码
    private String password;

    //mysql8
    private final String MYSQL8 = "com.mysql.cj.jdbc.Driver";

    //mysql5
    private final String MYSQL5 = "com.mysql.jdbc.Driver";

    //ojdbc6-11.2.0.3
    private final String ORACLE = "oracle.jdbc.driver.OracleDriver";
    //SQL Server
    private final String SQL_SERVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

    public JdbcUtil(String dataType, String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
        String driver = "";
        if (dataType.toLowerCase().equals(Constants.MySql.getValue().toLowerCase())) {
            driver = MYSQL8;
        } else if (dataType.toLowerCase().equals(Constants.Oracle.getValue().toLowerCase())) {
            driver = ORACLE;
        } else if (dataType.toLowerCase().equals(Constants.SqlServer.getValue().toLowerCase())) {
            driver = SQL_SERVER;
        }
        try {
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     *
     * @return 数据库连接
     */
    private Connection getConnection() {
        try {
            // 建立数据库连接对象
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 执行查询操作
     *
     * @param sql SQL语句
     * @return 返回值是一个结果集
     */
    public ResultSet executeQuery(String sql) {
        try {
            if (connection==null){
                connection = this.getConnection();
            }
            if (null != connection) {
                statement = connection.createStatement();
                if (null != statement) {
                    resultSet = statement.executeQuery(sql);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    /**
     * 关闭数据库连接
     */
    public void close() {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 处理resultSet结果集对象，并将结果集对象封装成 List<Map<String, Object>> 对象
     *
     * @param resultSet 结果集对象
     * @return 结果集合
     * @throws SQLException SQL异常
     */
    private static List<Map<String, Object>> getDates(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> dates = new ArrayList<>();
        // 获取结果集的数据结构对象
        ResultSetMetaData metaData = resultSet.getMetaData();
        while (resultSet.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                rowMap.put(metaData.getColumnName(i), resultSet.getObject(i));
            }
            dates.add(rowMap);
        }
        return dates;
    }

    /**
     * 执行增删改操作
     *
     * @param sql SQL语句
     * @return 返回值是改变数据的数量
     */
    public int execute(String sql) {
        int i = 0;
        try {
            // 获得连接
            connection = this.getConnection();
            // 获得语句执行者
            preparedStatement = connection.prepareStatement(sql);

            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }


    /* *************************** 以下为提供外部调用者的调用方法 ************************************/

    /**
     * 测试数据源是否可以连通
     *
     * @return 连通结果
     */
    public Boolean testDBConnect() {
        if (this.getConnection() != null) {
            // 关闭相关连接
            this.close();
            return true;
        } else {
            return false;
        }
    }

    /**
     * 执行查询语句 - 无参数
     *
     * @param querySql 查询语句
     * @return 结果集合
     */
    public List<Map<String, Object>> executeQuerySql(String querySql) {
        try {
            // 执行查询语句
            ResultSet rs = this.executeQuery(querySql);
            if (null != rs) {
                // 处理查询结果
                return getDates(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭相关连接
//            this.close();
        }
        return null;
    }
}


